package com.party.admin.biz.excel;

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Maps;
import com.party.admin.utils.RealmUtils;
import com.party.admin.utils.excel.ImportExcel;
import com.party.common.redis.StringJedis;
import com.party.common.utils.StringUtils;
import com.party.core.exception.BusinessException;
import com.party.core.model.YesNoStatus;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.TreeMap;

/**
 * 表格分析业务处理
 *
 * @author yifeng
 * @date 2018/6/12 0012
 * @time 下午 15:09
 */
@Service
public class ExcelAnalyzeBizService {
    @Autowired
    private StringJedis stringJedis;

    public static String REDIS_KEY_EXCEL_ANALYZE = "excel_analyze";
    private static Integer startRowNum = 0;
    private static Integer splitNum = 5;
    private static DecimalFormat decimalFormat = new DecimalFormat("#.##");
    private static String NULL_STR = "NULL";

    /**
     * 导入Excel，将信息存储在redis 24小时过期
     *
     * @param uploadFile 文件
     * @return
     * @throws Exception
     */
    public void importExcel(MultipartFile uploadFile) throws Exception {
        String fileName = uploadFile.getOriginalFilename();
        InputStream inputStream = uploadFile.getInputStream();
        Workbook workbook = ImportExcel.getWorkbook(inputStream, fileName);
        Map<String, Map<String, Map<String, String>>> sheetInfo = getExcelInfo(workbook);
        String memberId = RealmUtils.getCurrentUser().getId();
        stringJedis.setValue(REDIS_KEY_EXCEL_ANALYZE + "_" + memberId, JSONObject.toJSONString(sheetInfo), 1000 * 60 * 60 * 24);
    }

    /**
     * 获取表格信息
     *
     * @param workbook 表格
     */
    public static Map<String, Map<String, Map<String, String>>> getExcelInfo(Workbook workbook) {
        Map<String, Map<String, Map<String, String>>> sheetInfo = Maps.newHashMap();
        // 第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        // 从第四行开始 坐标为3

        // 表头行
        Row titleRow = sheet.getRow(startRowNum);
        // 遍历所有的行
        for (Integer rowNum = startRowNum + 1; rowNum < sheet.getLastRowNum() + 1; rowNum++) {
            Row row = sheet.getRow(rowNum);
            // 块数量
            Integer blockNum = 0;
            Integer splitIndex = 0;
            // 遍历所有的列
            for (int colNum = 0; colNum < titleRow.getLastCellNum(); colNum++) {
                Cell cell = row.getCell(colNum);
                String cellValue = ImportExcel.getCellValue(cell);

                if (StringUtils.isEmpty(cellValue)) {
                    cellValue = NULL_STR;
                }
                // 已5列为一块
                if (colNum % splitNum == 0) {
                    blockNum++;
                    splitIndex = 0;

                    // 行数据
                    Map<String, Map<String, String>> rowMap = sheetInfo.get(blockNum.toString());
                    if (rowMap == null) {
                        rowMap = new LinkedHashMap<>();
                    }
                    sheetInfo.put(blockNum.toString(), rowMap);

                    // 列数据
                    Map<String, String> colMap = rowMap.get(rowNum.toString());
                    if (colMap == null) {
                        colMap = new LinkedHashMap<>();
                    }
                    colMap.put(splitIndex.toString(), cellValue);
                    rowMap.put(rowNum.toString(), colMap);
                } else {
                    Map<String, Map<String, String>> rowMap = sheetInfo.get(blockNum.toString());
                    Map<String, String> colMap = rowMap.get(rowNum.toString());
                    colMap.put(splitIndex.toString(), cellValue);
                }
                splitIndex++;
            }
        }
        return sheetInfo;
    }

    /**
     * 计算表格数据 以列为单位
     * 如果填写了行号，计算该块对应行数据；如果没有填写行号，计算该块所有行数据
     * 如果填写了列号，计算该块该行对应列数据；如果没有填写列号，计算该块该行所有列数据
     *
     * @param blockNum 块
     * @param rowNums  行
     * @param colNums  列
     * @param fields   字段
     */
    public Map<String, Map<String, Object>> analyzeExcel(String blockNum, String rowNums, String colNums, String fields) {
        String memberId = RealmUtils.getCurrentUser().getId();
        String value = stringJedis.getValue(REDIS_KEY_EXCEL_ANALYZE + "_" + memberId);
        if (StringUtils.isEmpty(value)) {
            throw new BusinessException("表格信息为空，请重新上传");
        }
        Map<String, Map<String, Object>> returnDataMap = new LinkedHashMap<>();
        Map<String, Map<String, Map<String, String>>> sheetInfo = JSONObject.parseObject(value, Map.class);
        Map<String, Map<String, String>> rowMap = sheetInfo.get(blockNum);
        if (rowMap == null) {
            return returnDataMap;
        }

        for (String field : fields.split(",")) {
            field = field.trim();
            // 总数
            Integer totalNum = 0;
            // 成功数
            Integer successNum = 0;
            for (Map.Entry<String, Map<String, String>> rowData : rowMap.entrySet()) {
                boolean rowIsOk = false;
                if (StringUtils.isEmpty(rowNums)) {
                    rowIsOk = true;
                } else {
                    for (String rowNum : rowNums.split(",")) {
                        Integer newRowNum = Integer.valueOf(rowNum.trim()) + startRowNum;
                        if (rowData.getKey().equals(newRowNum.toString())) {
                            rowIsOk = true;
                            break;
                        }
                    }
                }
                if (rowIsOk) {
                    Map<String, String> colMap = new TreeMap<String, String>(rowData.getValue());
                    int index = 0;
                    // 状态 1：成功 0：失败
                    String status = "";
                    for (Map.Entry<String, String> colData : colMap.entrySet()) {
                        boolean colIsOk = false;
                        if (StringUtils.isEmpty(colNums)) {
                            colIsOk = true;
                        } else {
                            for (String colNum : colNums.split(",")) {
                                if (colData.getKey().equals(colNum)) {
                                    colIsOk = true;
                                    break;
                                }
                            }
                        }

                        String cellValue = colData.getValue();
                        if (index == 0) {
                            status = cellValue;
                        }

                        if (colIsOk) {
                            if (index != 0) {
                                if (field.equals(cellValue)) {
                                    totalNum++;
                                    if (status.equals(YesNoStatus.YES.getCode().toString())) {
                                        if (index != 4) {
                                            // 不是最后一位，检查后一位是不是空的
                                            String afterValue = colMap.get(index + 1 + "");
                                            if (NULL_STR.equals(afterValue)) {
                                                successNum++;
                                            }
                                        } else if (index == 4) {
                                            // 如果是最后一位，检查当前这块区域的这一行里面有没有空的，没有就默认成功
                                            boolean checkFlag = false;
                                            for (String cValue : colMap.values()) {
                                                if (NULL_STR.equals(cValue)) {
                                                    checkFlag = true;
                                                    break;
                                                }
                                            }
                                            if (!checkFlag) {
                                                successNum++;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        index++;
                    }
                }
            }
            Map<String, Object> statisticMap = Maps.newHashMap();
            statisticMap.put("totalNum", totalNum);
            statisticMap.put("successNum", successNum);
            returnDataMap.put(field, statisticMap);
        }
        return returnDataMap;
    }
}
